package com.siqi.hr.system.organization.dao.mysql;

import com.siqi.hr.common.Constants;
import com.siqi.hr.system.common.utils.Common;
import com.siqi.hr.system.organization.dao.intf.EmployeeDao;
import com.siqi.hr.system.organization.entity.Employee;
import org.apache.commons.lang.StringEscapeUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.util.Assert;

import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * 员工信息维护dao实现类层
 * @author     xiaogq
 * Created by xiaogoingqin on 2016/7/21.
 */
@Repository("employeeDao")
public class EmployeeDaoImpl implements EmployeeDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 根据登录名称获得用户信息
     * @param username(用户名)
     * @return
     * @throws Exception
     */
    @Override
    public Employee queryEmpAndDepAndRoleByLogUsername(String username) throws Exception{
        Assert.hasText(username);
        Employee emp = null;
        StringBuffer sql = new StringBuffer();
        sql.append("select t3.id as t3_id,t3.name_varchar as t3_name_varchar,t3.job_number_varchar as t3_job_number_varchar, ");
        sql.append(" t3.password_varchar as t3_password_varchar,t3.pk_t_1_1 as t3_pk_t_1_1,t3.pk_t_2_1 as t3_pk_t_2_1, ");
        sql.append(" t3.head_img as t3_head_img,t3.flag_bit as t3_flag_bit, ");

        sql.append(" t2.name_varchar as t2_name_varchar,t2.create_date_datetime as t2_create_date_datetime, ");
        sql.append(" t2.update_date_timestamp as t2_update_date_timestamp,t2.pk_t_2_1 as t2_pk_t_2_1,t2.dept_rank_varchar as t2_dept_rank_varchar, ");
        sql.append(" t2.flag_bit as t2_flag_bit,t2.dept_address_varchar as t2_dept_address_varchar, ");
        sql.append(" t2.isparent_bit as t2_isparent_bit,t2.icon_varchar as t2_icon_varchar,t2.dept_description_textarea as t2_dept_description_textarea,");

        sql.append(" t1.loginModule_varchar as t1_loginModule_varchar,t1.isadmin_bit as t_1_isadmin ");
        sql.append(" from t_3 as t3 LEFT JOIN t_2 as t2 on t3.pk_t_2_1 = t2.id left join t_1 as t1 on t3.pk_t_1_1 = t1.id ");
        sql.append(" where job_number_varchar = ? or job_number_varchar = ? ");
        try {
            emp = jdbcTemplate.queryForObject(sql.toString(),new Object[] { StringEscapeUtils.escapeSql(username),StringEscapeUtils.escapeSql(username.toLowerCase()) },new Employee.empAndDepAndRoleField());
        } catch (Exception e) {
            e.printStackTrace();
        }
        return emp;
    }

    /**
     * @param empId       当前登录系统的员工id
     * @throws Exception
     */
    @Override
    public void updateEmpLoginDate(String empId) throws Exception{
        String sql = "update t_3 set latest_login_datetime = ? where id = ?";
        jdbcTemplate.update(sql,new Object[] { new Date(), empId });
    }

    @Override
    public Boolean loginPwdConfirm(String empid, String pwd) {
        Assert.hasText(empid);
        Assert.hasText(pwd);
        try {
            String sql = "select password_varchar from t_3 where id = '" + empid + "' ";
            String emppwd = jdbcTemplate.queryForObject(sql.toString(), String.class);
            emppwd = (emppwd == null) || (emppwd.equals("")) ? Common.Md5("") : emppwd;
            if (emppwd.equals(pwd))
                return Boolean.valueOf(true);
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        return Boolean.valueOf(false);
    }

    @Override
    public void updatePwd(String empid, String pwd) {
        Assert.hasText(empid);
        Assert.hasText(pwd);
        try {
            String sql = "update t_3 set password_varchar = ?, pass_update_date_datetime = now() where id = ? ";
            jdbcTemplate.update(sql,new Object[] { pwd, empid });
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void emptyEmployeePassword(String employeeId) {
        Assert.hasText(employeeId);
        String sql = "update t_3 set password_varchar = null where id = '" + employeeId + "' ";
        jdbcTemplate.update(sql);
    }

    @Override
    public int getMaxJobNumber(String tableName) {
        StringBuffer sql = new StringBuffer("select max(count_int) from "+ tableName);
        return jdbcTemplate.queryForObject(sql.toString(),Integer.class);
    }

    @Override
    public void delEmpAndDeptRelation(String empId) {
        StringBuffer sql = new StringBuffer("delete from z_3_2 where host_record_id = '").append(empId).append("'");
        jdbcTemplate.execute(sql.toString());
    }

    @Override
    public long getCountByPostId(String postId,String empTableName,String empPostTableName) {
        String sql = "select count(z.id) from "+empPostTableName+" as z left join "+empTableName+" as e on z.host_record_id = e.id where z.related_record_id = '"+postId+"' and e.flag_bit = false and e.jobstatus_singlebox != 21 and e.id != '"+ Constants.ROOTEMPID+"' ";
        long count = 0;
        try{
            count = jdbcTemplate.queryForObject(sql,Integer.class);
        }catch (Exception e){
            count = 0;
            e.printStackTrace();
        }
        return count;
    }

    @Override
    public List<Map<String, Object>> getEmpListByPostId(String postId, String empTableName, String deptTableName,String zEmpPostTableName) {
        String sql = "select t3.id as id, left (t3.name_varchar, 1) as lastName, t3.name_varchar as name, t3.job_number_varchar as jobNumber, " +
                " t2.id as deptId, t2.name_varchar as deptName, t2.dept_rank_varchar as deptRank from "+zEmpPostTableName+" as zp left join " +
                 empTableName+" as t3 on zp.host_record_id = t3.id left join "+deptTableName+" as t2 on t3.pk_t_2_1 = t2.id " +
                " where zp.related_record_id ='"+postId+"' and t3.id != '"+Constants.ROOTEMPID+"' and t3.flag_bit = false and t3.jobstatus_singlebox != 21 ";//员工启停标志 0为启用,员工离职为21
        try {
            return jdbcTemplate.queryForList(sql);
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Map<String, Object>> searchEmpByPostId(String empTableName,String deptTableName,String postId,String search) {
        String sql = "select t3.id as id, left (t3.name_varchar, 1) as lastName, t3.name_varchar as name, t3.job_number_varchar as jobNumber, " +
                " t2.id as deptId, t2.name_varchar as deptName, t2.dept_rank_varchar as deptRank from "+empTableName+" as t3  left join "+deptTableName+" as t2 on t3.pk_t_2_1 = t2.id " +
                " where t3.pk_t_4_1 ='"+postId+"' and t3.id != '"+Constants.ROOTEMPID+"' and t3.flag_bit = false and t3.jobstatus_singlebox != 21 and t3.name_varchar like '%"+search+"%' ";//员工启停标志 0为启用,员工离职为21
        try {
            return jdbcTemplate.queryForList(sql);
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Map<String, Object>> queryRelationUsers(String tableName, String empDeptTableName, String empTableName) {
        String sql = "select u.id, u.name_varchar, CONCAT(',', GROUP_CONCAT(d.dept_rank_varchar separator '')) AS u_dept_id from "+ empDeptTableName +" ud, "+ empTableName +" u, "+ tableName +" d where ud.host_record_id = u.id and ud.related_record_id = d.id and u.flag_bit = false group by u.id";
        try {
            return jdbcTemplate.queryForList(sql);
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Map<String, Object>> getAllEmpName(String tableName) {
        StringBuffer sql = new StringBuffer("select id,name_varchar as name from ").append(tableName).append(" where flag_bit = false and id != '").append(Constants.ROOTEMPID).append("'");
        List<Map<String, Object>> empList = jdbcTemplate.queryForList(sql.toString());
        return empList;
    }
}
